/**
 * 文件名：StatSkuOutDetailMonthlyService.java
 *
 * 创建人：Fyh - 1009173473@qq.com
 *
 * 创建时间：2019年3月11日 下午5:03:03
 *
 * 版权所有：南京植百汇智能科技服务有限公司
 */
package com.z100h.supplychain.statistics.service;

import java.util.List;

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.z100h.supplychain.statistics.entity.StatSkuOutDetailMonthly;
import com.z100h.supplychain.statistics.entity.StatTaskMonthly;
import com.z100h.supplychain.statistics.mapper.StatSkuOutDetailMonthlyMapper;
import com.z100h.supplychain.statistics.mapper.StatTaskMonthlyMapper;

/**
 * [描述信息：说明类的基本功能]
 *
 * @author Fyh - 1009173473@qq.com
 * @version 1.0 Created on 2019年3月11日 下午5:03:03
 */
@Service
@Transactional(readOnly = true)
public class StatSkuOutDetailMonthlyService {

	@Autowired
	private StatSkuOutDetailMonthlyMapper statSkuOutDetailMonthlyMapper;
	
	@Autowired
	private StatTaskMonthlyMapper statTaskMonthlyMapper;
	
	public List<StatSkuOutDetailMonthly> get(String id){
		return statSkuOutDetailMonthlyMapper.get(id);
	}

	public Workbook createWorkbook(String id) {
		List<StatSkuOutDetailMonthly> list=statSkuOutDetailMonthlyMapper.get(id);
		StatTaskMonthly task=statTaskMonthlyMapper.get(id);
		//step1 创建工作簿
		 Workbook workbook=new HSSFWorkbook();	
		 
		 CellStyle style = workbook.createCellStyle();
		 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		 
		 Sheet sheet=workbook.createSheet();
		 sheet.setColumnWidth(0, 3766);
		 sheet.setColumnWidth(1, 7000);
		 sheet.setColumnWidth(2, 3766);
		 sheet.setColumnWidth(3, 3766);
		 
		 
		 
		 
		 //step2 创建第一行表头
		 Row firstRow=sheet.createRow(0);
		 Row secondRow=sheet.createRow(1);
		 Row thirdRow=sheet.createRow(2);
		 
		 firstRow.createCell(0).setCellValue("南京植百汇智能科技服务有限公司");
		 secondRow.createCell(0).setCellValue(task.getTaskMonth()+"出库总表");
		 thirdRow.createCell(0).setCellValue("序号");
		 thirdRow.createCell(1).setCellValue("sku");
		 thirdRow.createCell(2).setCellValue("已出库数量");
		 thirdRow.createCell(3).setCellValue("备注");
		 CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, 3);
		 CellRangeAddress region2 = new CellRangeAddress(1, 1, 0, 3);
	     sheet.addMergedRegion(region1);
	     sheet.addMergedRegion(region2);
	     
	     thirdRow.getCell(0).setCellStyle(style);
	     thirdRow.getCell(1).setCellStyle(style);
	     thirdRow.getCell(2).setCellStyle(style);
	     thirdRow.getCell(3).setCellStyle(style);
	     

		HSSFFont font = (HSSFFont) workbook.createFont();  
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示  
		font.setFontHeightInPoints((short) 14); 
		
		CellStyle style1 = workbook.createCellStyle();
		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style1.setFont(font);
		firstRow.getCell(0).setCellStyle(style1);
	    secondRow.getCell(0).setCellStyle(style1);
	     
	    Row row;
	    if(CollectionUtils.isNotEmpty(list)) {
	    	for(int i=0;i<list.size();i++) {
	    		row=sheet.createRow(i+3);
	    		row.createCell(0).setCellValue(i+1);
	    		row.createCell(1).setCellValue(list.get(i).getSkuInfo());
	    		row.createCell(2).setCellValue(list.get(i).getAmount());
	    		sheet.getRow(i+3).getCell(0).setCellStyle(style);
	    		sheet.getRow(i+3).getCell(1).setCellStyle(style);
	    		sheet.getRow(i+3).getCell(2).setCellStyle(style);
	    	}
	    }     
		return workbook;
	}
	
//	public static void main(String[] args) throws IOException {
//		 //step1 创建工作簿
//		 Workbook workbook=new HSSFWorkbook();	
//		 
//		 CellStyle style = workbook.createCellStyle();
//		 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//		 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//		 
//		 Sheet sheet=workbook.createSheet();
//		 sheet.setColumnWidth(0, 3766);
//		 sheet.setColumnWidth(1, 3766);
//		 sheet.setColumnWidth(2, 3766);
//		 sheet.setColumnWidth(3, 3766);
//		 
//		 
//		 
//		 
//		 //step2 创建第一行表头
//		 Row firstRow=sheet.createRow(0);
//		 Row secondRow=sheet.createRow(1);
//		 Row thirdRow=sheet.createRow(2);
//		 
//		 firstRow.createCell(0).setCellValue("南京植百汇智能科技服务有限公司");
//		 secondRow.createCell(0).setCellValue("出库总表");
//		 thirdRow.createCell(0).setCellValue("序号");
//		 thirdRow.createCell(1).setCellValue("sku");
//		 thirdRow.createCell(2).setCellValue("已出库数量");
//		 thirdRow.createCell(3).setCellValue("备注");
//		 CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, 3);
//		 CellRangeAddress region2 = new CellRangeAddress(1, 1, 0, 3);
//	     sheet.addMergedRegion(region1);
//	     sheet.addMergedRegion(region2);
//	     
//	     thirdRow.getCell(0).setCellStyle(style);
//	     thirdRow.getCell(1).setCellStyle(style);
//	     thirdRow.getCell(2).setCellStyle(style);
//	     thirdRow.getCell(3).setCellStyle(style);
//	     
//
//		HSSFFont font = (HSSFFont) workbook.createFont();  
//		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示  
//		font.setFontHeightInPoints((short) 14); 
//		
//		CellStyle style1 = workbook.createCellStyle();
//		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//		style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//		style1.setFont(font);
//		firstRow.getCell(0).setCellStyle(style1);
//	    secondRow.getCell(0).setCellStyle(style1);
//	     
//	     //step4 多态下载数据
//			try {
//				 String name = "batch_excel";
//		         File fileBatch = new File("E:/");
//		         if(!fileBatch.exists()){
//		             fileBatch.mkdir();
//		         }
//		         //默认导出到D盘下
//		         FileOutputStream out = null;
//				try {
//					out = new FileOutputStream(fileBatch+"/"+name+".xls");
//				} catch (FileNotFoundException e) {
//					// TODO Auto-generated catch block
//					e.printStackTrace();
//				}
//		         workbook.write(out);
//		         out.flush();
//		         out.close();// 关闭流
//			} finally {
//			   
//			}
//			System.out.println("222");
//	}
}
